import pandas as pd
import numpy as np
import json

df = pd.read_excel('1.xlsx')
#行数
lines=len(df.index.values)
#列数
columns=len(df.columns.values)
print(lines,columns)

data={}
for i in range(lines):
    dict={}
    date=str(df.iloc[i,1]).split(' ')[0]
    if date not in data:
        data[date]=[]
    for j in range(columns):
        item=df.iloc[i,j]
        if item!=item:
            continue
        if j!=1:
            dict[df.columns.values[j]]=str(item)
    data[date].append(dict)
'''        
        if j==1:
            dict["year"]=item.year
            dict["month"]=item.month
            dict["day"]=item.day
            print(str(item).split(' ')[0])
        else:
            dict[df.columns.values[j]]=str(item)
    data.append(dict)
'''
js=json.dumps(data,sort_keys=False,ensure_ascii=False,indent=1)
# 可读可写，如果不存在则创建，如果有内容则覆盖
jsFile = open("data.json", "w+", encoding='utf-8')
jsFile.write(js)
jsFile.close()
